package com.r7data.dbstruct.meta;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.r7data.dbstruct.code.DbConstantCode;
import com.r7data.dbstruct.code.ValueMetaCode;
import com.r7data.dbstruct.config.DbSource;
import com.r7data.dbstruct.entity.ColumnMetaInfo;
import com.r7data.dbstruct.entity.DbConnectionInfo;
import com.r7data.dbstruct.exception.DbStructException;

/**
 * 基础 meta 类
 * @author admin
 */
public abstract class BaseDbMeta {
	
	protected int dbType;
	/** 表查询语句模板 **/
	protected String tableSearchSqlTmp;

	public String getTableSearchSqlTmp() {
		return tableSearchSqlTmp;
	}
	public void setTableSearchSqlTmp(String tableSearchSqlTmp) {
		this.tableSearchSqlTmp = tableSearchSqlTmp;
	}

	public int getDbType() {
		return dbType;
	}

	public void setDbType(int dbType) {
		this.dbType = dbType;
	}
	
	/**
	 * 获取表
	 * @param con
	 * @param dbName
	 * @return
	 */
	public List<String> getTables(Connection con, String dbName){
		
		List<String> tableList = new ArrayList<String>();

		ResultSet rst = null;

		try {
			DatabaseMetaData dbmd = con.getMetaData();
			
			rst = getTableResult(dbmd, dbName);

			while (rst.next()) {

				String tableName = rst.getString(3);

				tableList.add(tableName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbSource.closeDb(null, null, rst);
		}
		
		return tableList;
	}
	
	/**
	 * 获取视图
	 */
	public List<String> getViews(Connection con, String dbName){
		
		List<String> tableList = new ArrayList<String>();

		ResultSet rst = null;

		try {
			DatabaseMetaData dbmd = con.getMetaData();
			
			rst = getViewResult(dbmd, dbName);

			while (rst.next()) {

				String tableName = rst.getString(3);

				tableList.add(tableName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbSource.closeDb(null, null, rst);
		}
		
		return tableList;
	}
	
	/**
	 * 获取数据库对应驱动
	 * 没有实际价值
	 * @param dbType
	 * @return
	 */
	public String getDriverClass(int dbType){
		return null;
	}
	
	
	
	protected ColumnMetaInfo getValueFromSQLType(ResultSetMetaData rdms, int index) throws Exception {
		
		ColumnMetaInfo columnInfo = null;
		
		try {
			int length = -1;
			int precision = -1;
			int valtype = ValueMetaCode.TYPE_NONE;

			boolean isClob = false;

			// 列名
			String fieldName = rdms.getColumnName(index);

			// 类型
			int type = rdms.getColumnType(index);

			// 是否有符号
			boolean signed = rdms.isSigned(index);

			switch (type) {
			case java.sql.Types.CHAR:
			case java.sql.Types.VARCHAR:
			case java.sql.Types.NVARCHAR:
			case java.sql.Types.LONGVARCHAR:{ // Character Large Object
				valtype = ValueMetaCode.TYPE_STRING;

				length = rdms.getColumnDisplaySize(index);

				break;
			}
			case java.sql.Types.CLOB:
			case java.sql.Types.NCLOB:
				valtype = ValueMetaCode.TYPE_STRING;

				length = ValueMetaCode.CLOB_LENGTH;

				isClob = true;
				break;

			case java.sql.Types.BIGINT:
				// verify Unsigned BIGINT overflow!
				if (signed) {
					valtype = ValueMetaCode.TYPE_INTEGER;

					precision = 0;

					length = 15;
				} else {
					valtype = ValueMetaCode.TYPE_BIGNUMBER;

					precision = 0; // Max 18.446.744.073.709.551.615
					length = 16;
				}
				break;

			case java.sql.Types.INTEGER:
				valtype = ValueMetaCode.TYPE_INTEGER;

				precision = 0; // Max 2.147.483.647
				length = 9;
				break;

			case java.sql.Types.SMALLINT:
				valtype = ValueMetaCode.TYPE_INTEGER;

				precision = 0; // Max 32.767
				length = 4;
				break;

			case java.sql.Types.TINYINT:
				valtype = ValueMetaCode.TYPE_INTEGER;

				precision = 0; // Max 127
				length = 2;
				break;

			case java.sql.Types.DECIMAL:
			case java.sql.Types.DOUBLE:
			case java.sql.Types.FLOAT:
			case java.sql.Types.REAL:
			case java.sql.Types.NUMERIC:
				valtype = ValueMetaCode.TYPE_NUMBER;

				length = rdms.getPrecision(index);
				precision = rdms.getScale(index);
				if (length >= 126) {
					length = -1;
				}
				if (precision >= 126) {
					precision = -1;
				}

				if (type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT || type == java.sql.Types.REAL) {
					if (precision == 0) {
						precision = -1; // precision is obviously incorrect if
										// the type if
						// Double/Float/Real
					}

					// MySQL: max resolution is double precision floating point
					// (double)
					// The (12,31) that is given back is not correct
					if (DbConstantCode.database_mysql == dbType) {
						if (precision >= length) {
							precision = -1;
							length = -1;
						}
					}

					// if the length or precision needs a BIGNUMBER
					if (length > 15 || precision > 15) {
						valtype = ValueMetaCode.TYPE_BIGNUMBER;

					}
				} else {
					if (precision == 0) {
						if (length <= 18 && length > 0) { // Among others Oracle
															// is affected
							// here.
							valtype = ValueMetaCode.TYPE_INTEGER; // Long
																	// can
																	// hold
																	// up to
																	// 18
							// significant digits
						} else if (length > 18) {
							valtype = ValueMetaCode.TYPE_BIGNUMBER;

						}
					} else { // we have a precision: keep NUMBER or change to
								// BIGNUMBER?
						if (length > 15 || precision > 15) {
							valtype = ValueMetaCode.TYPE_BIGNUMBER;

						}
					}
				}


				if (DbConstantCode.database_oracle == dbType) {
					if (precision == 0 && length == 38) {
						valtype = ValueMetaCode.TYPE_INTEGER;

					}
					if (precision <= 0 && length <= 0) {
						// undefined size: BIGNUMBER,
						// precision on Oracle can be 38, too
						// big for a Number type
						valtype = ValueMetaCode.TYPE_BIGNUMBER;

						length = -1;
						precision = -1;
					}
				}

				break;

			case java.sql.Types.TIMESTAMP:
				// if (databaseMeta.supportsTimestampDataType()) {
				if (true) {
					valtype = ValueMetaCode.TYPE_TIMESTAMP;

					length = rdms.getScale(index);
				}
				break;

			
			case java.sql.Types.TIME:
				valtype = ValueMetaCode.TYPE_DATE;

				//
				if (DbConstantCode.database_mysql == dbType) {
					// String property =
					// databaseMeta.getConnectionProperties().getProperty("yearIsDateType");
					if (rdms.getColumnTypeName(index).equalsIgnoreCase("YEAR")) {
						valtype = ValueMetaCode.TYPE_INTEGER;

						precision = 0;
						length = 4;
						break;
					}
				}
				break;

			case java.sql.Types.BOOLEAN:
			case java.sql.Types.BIT:
				valtype = ValueMetaCode.TYPE_BOOLEAN;

				break;

			case java.sql.Types.BINARY:
			case java.sql.Types.BLOB:
			case java.sql.Types.VARBINARY:
			case java.sql.Types.LONGVARBINARY:
				valtype = ValueMetaCode.TYPE_BINARY;

				
				if (DbConstantCode.database_oracle == dbType
						&& (type == java.sql.Types.VARBINARY || type == java.sql.Types.LONGVARBINARY)) {
					// set the length for Oracle "RAW" or "LONGRAW" data types
					valtype = ValueMetaCode.TYPE_STRING;

					length = rdms.getColumnDisplaySize(index);
					
				} else if (DbConstantCode.database_mysql == dbType
						&& (type == java.sql.Types.VARBINARY || type == java.sql.Types.LONGVARBINARY)) {
					// set the data type to String, see PDI-4812
					valtype = ValueMetaCode.TYPE_STRING;

					// PDI-6677 - don't call 'length =
					// rm.getColumnDisplaySize(index);'
					length = -1; // keep the length to -1, e.g. for string
									// functions (e.g.
					// CONCAT see PDI-4812)
				} else {
					length = -1;
				}
				precision = -1;
				break;

			default:
				valtype = ValueMetaCode.TYPE_STRING;

				precision = rdms.getScale(index);
				break;
			}

			/** =============================== **/
			// Grab the comment as a description to the field as well.
			String comments = rdms.getColumnLabel(index);
			

			// get & store more result set meta data for later use
			int originalColumnType = rdms.getColumnType(index);
			
			String originalColumnTypeName = rdms.getColumnTypeName(index);
			
			int originalPrecision = rdms.getPrecision(index);
 			
			int originalScale = rdms.getScale(index);
	
			boolean originalSigned = false;
			try {
				originalSigned = rdms.isSigned(index);
			} catch (Exception ignored) {
				// This JDBC Driver doesn't support the isSigned method.
				// Nothing more we can do here.
			}
			
			/** =============================== **/

			// See if we need to enable lazy conversion...
			// mysql is false
			 
			columnInfo = new ColumnMetaInfo();
			
			columnInfo.setFieldName(fieldName);
			columnInfo.setValtype(valtype);
			columnInfo.setLength(length);
			columnInfo.setPrecision(precision);
			//Const.NVL( v.getOrigin(), "" ) );
			//ValueMetaBase.getStorageTypeCode( v.getStorageType() ) );
			columnInfo.setLargeTextField(isClob);
			
			columnInfo.setComments(comments);
			columnInfo.setOriginalColumnType(originalColumnType);
			columnInfo.setOriginalColumnTypeName(originalColumnTypeName);
			columnInfo.setOriginalPrecision(originalPrecision);
			columnInfo.setOriginalScale(originalScale);
			columnInfo.setOriginalSigned(originalSigned);

			columnInfo.setDefaultConversionMask();
			
			
		} catch (Exception e) {
			throw new Exception("Error determining value metadata from SQL resultset metadata", e);
		}
		return columnInfo;
	}

	/**
	 * 获取对应数据库连接
	 * @param dbType
	 * @param ipAddress
	 * @param dbUser
	 * @param dbPassword
	 * @param database
	 * @return
	 */
	public Connection getDbCon(DbConnectionInfo dbInfo) throws Exception{
		Connection con = createCon(dbInfo);
		
		return con;
	}
	
	/**
	 * 获得数据库连接
	 * @param dbInfo
	 * @return
	 * @throws Exception
	 */
	protected Connection createCon(DbConnectionInfo dbInfo) throws Exception{
		
		String dbUrl = getDbUrl(dbInfo);
		
		String dbUser = dbInfo.getDbUser();
		String dbPassword = dbInfo.getDbPassword();
		
		//Class.forName(className);//经测试不需要
		
		Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
		
		return con;
	}
	
	
	/**
	 * 获得 所有的 databases
	 * @param con
	 * @return
	 */
	public List<String> getDatabases(Connection con){
		
		ResultSet rst = null;

		List<String> dbList = new ArrayList<String>();
		
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			
			rst = dbmd.getCatalogs();

			while (rst != null && rst.next()) {
				
				String dbName = rst.getString(1);
				
				dbList.add(dbName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return dbList;
	}
	
	/**
	 * 获得 table 查询语句
	 * @param dbName
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public String getTableSql(String dbName, String tableName) throws Exception{
		String sql = null;
		if(dbName == null){
			throw new DbStructException("database name can not be null!");
			
		}else if(tableName == null){
			throw new DbStructException("table name can not be null!");
		}else{
			dbName = escapeField(dbName);
			tableName = escapeField(tableName);
			
			String sqlTmp = getTableSearchSqlTmp();
			
			sql = String.format(sqlTmp, dbName, tableName);
		}
		return sql;
	}
	
	/**
	 * 获取表结构列信息
	 * @param con
	 * @param dbName
	 * @param tableName
	 * @return
	 */
	public List<ColumnMetaInfo> getColumnInfoList(Connection con, String dbName, String tableName){
		
		List<ColumnMetaInfo> columnList = new ArrayList<ColumnMetaInfo>();
		
		PreparedStatement pst = null;
		ResultSet rst = null;
		
		try {
			String sql = getTableSql(dbName, tableName);
			
			pst = con.prepareStatement(sql);
			rst = pst.executeQuery();
			
			pst.setMaxRows(1);
			
			ResultSetMetaData rsmd = pst.getMetaData();
			
			int colCount = rsmd.getColumnCount();
			
			for(int i = 1; i <= colCount; i++){
				
				ColumnMetaInfo columnInfo = getValueFromSQLType(rsmd, i);
				
				columnList.add(columnInfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DbSource.closeDb(null, pst, rst);
		}
		
		return columnList;
	}
	
	/**
	 * 获取表名 resultset
	 * @param dbmd
	 * @param dbName
	 * @return
	 * @throws Exception
	 */
	public abstract ResultSet getTableResult(DatabaseMetaData dbmd, String dbName) throws SQLException;
	
	/**
	 * 获取 视图 resultSet
	 * @param dbmd
	 * @param schemaName
	 * @return
	 * @throws SQLException
	 */
	public abstract ResultSet getViewResult(DatabaseMetaData dbmd, String schemaName) throws SQLException;
	
	/**
	 * 生成数据库 url
	 * @param dbInfo
	 * @return
	 */
	public abstract String getDbUrl(DbConnectionInfo dbInfo);
	
	/**
	 * 特殊字端处理
	 * @param field
	 * @return
	 */
	public abstract String escapeField(String field);
	
}


